USE TVC_WEB
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.[procPayment_add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[procPayment_add]
GO
--CREATEd:02/08/2010
--Author: nnamthach@gmail.com
CREATE procedure dbo.[procPayment_add] (
@Code as nchar(10),
@Name as varchar(50),
@NameVN as nvarchar(50),
@Value1 as float,
@Value2 as float,
@Value3 as float
)
AS
BEGIN
INSERT INTO dbo.[Payment](
Code,	
Name,	
NameVN,	
Value1,	
Value2,	
Value3	
)
VALUES(
@Code,
@Name,
@NameVN,
@Value1,
@Value2,
@Value3
	
	)
SELECT SCOPE_IDENTITY()
END
GO

if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.[procPayment_update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[procPayment_update]
GO
--CREATEd:02/08/2010
--Author: nnamthach@gmail.com
CREATE procedure dbo.[procPayment_update](
@ID as int,
@Code as nchar(10),
@Name as varchar(50),
@NameVN as nvarchar(50),
@Value1 as float,
@Value2 as float,
@Value3 as float
)
AS
BEGIN
UPDATE dbo.[Payment] SET 
	Code = @Code,
	Name = @Name,
	NameVN = @NameVN,
	Value1 = @Value1,
	Value2 = @Value2,
	Value3 = @Value3
	
WHERE 	(
		@ID = ID 
 )
END
GO

if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.[procPayment_delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[procPayment_delete]
GO
--CREATEd:02/08/2010
--Author: nnamthach@gmail.com
CREATE procedure dbo.[procPayment_delete](
@ID as int
)
AS
BEGIN 
DELETE dbo.[Payment]
WHERE 	(
		@ID = ID 
 )
END
GO

if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.[procPayment_isexist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[procPayment_isexist]
GO
--CREATEd:02/08/2010
--Author: nnamthach@gmail.com
CREATE procedure dbo.[procPayment_isexist](
@ID as int
)
AS
BEGIN
SELECT TOP 1
	ID
	
FROM  dbo.[Payment]
WHERE 	(
		@ID = ID 
)
END
GO

if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.[procPayment_get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[procPayment_get]
GO
--CREATEd:02/08/2010
--Author: nnamthach@gmail.com
CREATE procedure dbo.[procPayment_get](
@ID as int
)
AS
BEGIN 
SELECT
	ID,
	Code,
	Name,
	NameVN,
	Value1,
	Value2,
	Value3
	
FROM  dbo.[Payment]
WHERE 	(
		@ID = ID 
)
END
GO

if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.[procPayment_getall]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[procPayment_getall]

GO
--CREATEd:02/08/2010
--Author: nnamthach@gmail.com
CREATE procedure dbo.[procPayment_getall]
AS
BEGIN
SELECT 	
	ID,
	Code,
	Name,
	NameVN,
	Value1,
	Value2,
	Value3
	
FROM  dbo.[Payment]
END
GO

if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.[procPayment_getcount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[procPayment_getcount]

GO
--CREATEd:02/08/2010
--Author: nnamthach@gmail.com
CREATE procedure dbo.[procPayment_getcount]
AS
BEGIN
SELECT 	
	Count(*)
	
FROM  dbo.[Payment]
END
GO

if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.[procPayment_getindex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[procPayment_getindex]

GO
--CREATEd:02/08/2010
--Author: nnamthach@gmail.com
CREATE procedure dbo.[procPayment_getindex]
@INX as int
AS
BEGIN
SELECT 	Rank,
		ID,
		Code,
		Name,
		NameVN,
		Value1,
		Value2,
		Value3
FROM
	(SELECT 	rank() over(Order by 
									ID
							) as Rank,
		ID,
		Code,
		Name,
		NameVN,
		Value1,
		Value2,
		Value3
		
	FROM  dbo.[Payment]) as tblRank
WHERE @INX = Rank
END
GO


if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.[procPayment_getpaged]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[procPayment_getpaged]
GO
--CREATEd:02/08/2010
--Author: nnamthach@gmail.com
CREATE procedure dbo.[procPayment_getpaged](
@WhereClause nvarchar (4000)  ,
@OrderBy varchar (2000)  ,
@PageIndex int   ,
@PageSize int  
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize
CREATE Table #PageIndex
(
 [IndexId] int IDENTITY (1, 1) NOT NULL,
	ID as int
)
DECLARE @SQL as nvarchar(4000)
SET @SQL = 'INSERT INTO #PageIndex (
ID
)'
SET @SQL = @SQL + ' SELECT'
IF @PageSize > 0
BEGIN
SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
End 
SET @SQL = @SQL + ' 
ID
'
SET @SQL = @SQL + ' FROM dbo.[Payment]'
IF LEN(@WhereClause) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
End
IF LEN(@OrderBy) > 0
BEGIN
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
End
exec sp_executesql @SQL
SELECT 	
	O.ID,
	O.Code,
	O.Name,
	O.NameVN,
	O.Value1,
	O.Value2,
	O.Value3
	
FROM  dbo.[Payment] AS O, [#PageIndex] AS PageIndex
WHERE     (PageIndex.IndexID > @PageLowerBound)  AND (
	O.ID = PageIndex.ID 
)
ORDER BY PageIndex.IndexID
SET @SQL = 'SELECT COUNT(*) as TotalRecords'
SET @SQL = @SQL + ' FROM dbo.[Payment]'
IF LEN(@WhereClause) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
End
exec sp_executesql @SQL
END 
GO

